package main.java.com.fxy.dao.impl;

import main.java.com.fxy.dao.CostDao;
import main.java.com.fxy.pojo.Cost;
import main.java.com.fxy.util.DBUtils;

import javax.servlet.http.HttpSession;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class CostDaoImpl implements CostDao {
    @Override
    public void deleteById(Integer id) {
        String sql ="delete from cost where id = '"+id+"'";
        try {
            DBUtils.getConn().prepareStatement(sql).execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Integer getIdByName(String user) {
        String sql ="select id from user where username = '"+user+"'";
        int id=0;
        try {
            ResultSet rs = DBUtils.getConn().prepareStatement(sql).executeQuery();
            rs.next();
            id=rs.getInt("id");//获取查询结果中的id
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }

    @Override
    public List<Cost> list(int userId) {
        String sql ="select * from cost where user_id = '"+userId+"'";
        List<Cost> list = new ArrayList<>();
        try {
            ResultSet rs = DBUtils.getConn().prepareStatement(sql).executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");//获取查询结果中的id
                String name = rs.getString("name");//获取查询结果中的name
                Double money = rs.getDouble("money");//获取查询结果中的money
                Date date = rs.getDate("date"); //获取查询结果中的date
                Cost cost = new Cost(id,name,money,date);//调用构造方法赋值
                list.add(cost);//添加到list集合中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Cost> query(String keyword,int userId) {
        String sql ="select * from cost WHERE user_id = '"+userId+"' and (name LIKE '%"+keyword+"%' OR money LIKE '%"+keyword+"%'OR date LIKE '%"+keyword+ "%')";
        List<Cost> list = new ArrayList<>();
        try {
            ResultSet rs = DBUtils.getConn().prepareStatement(sql).executeQuery();
            while (rs.next()) {
                    int id = rs.getInt("id");//获取查询结果中的id
                    String name = rs.getString("name");//获取查询结果中的name
                    Double money = rs.getDouble("money");//获取查询结果中的money
                    Date date = rs.getDate("date"); //获取查询结果中的date

                    Cost cost = new Cost(id,name,money,date);//调用构造方法赋值
                    list.add(cost);//添加到list集合中
                    rs.close();
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("8");
        return list;
    }

    @Override
    public void update(Cost cost) {
        String dateFormat = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
        String formattedDate = sdf.format(cost.getDate()); // 格式化日期
        String sql ="update cost set name ='" + cost.getName() + "', money='"+ cost.getMoney()+ "', date='"+ formattedDate+ "', detail='"+ cost.getDetail()+ "' where id='" + cost.getId() + "'";

        try {
            DBUtils.getConn().prepareStatement(sql).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Cost getCostById(int id) {
        String sql = "select * from cost where id ='" + id + "'";
        Cost cost = null;
        try {
            ResultSet rs = DBUtils.getConn().prepareStatement(sql).executeQuery();
            while (rs.next()) {
                String name = rs.getString("name");
                Double money = rs.getDouble("money");
                Date date = rs.getDate("date");
                String detail = rs.getString("detail");
                cost = new Cost(id,name,money,date,detail);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cost;
    }

    @Override
    public void addCost(Cost cost) {
        String dateFormat = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
        String formattedDate = sdf.format(cost.getDate()); // 格式化日期

        String sql ="insert into cost(name,money,date,detail,user_id) values('"+cost.getName()+"','"+cost.getMoney()+"','"+formattedDate+"','"+cost.getDetail()+"','"+cost.getUserId()+"')";
        System.out.println(sql);
        try {
            DBUtils.getConn().prepareStatement(sql).execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
